const { pool } = require('../config/database');

// ==================== 简单的商品详情内存缓存 ====================
// TTL 默认60秒，可通过环境变量 GOODS_DETAIL_CACHE_TTL_MS 调整
const DEFAULT_CACHE_TTL_MS = parseInt(process.env.GOODS_DETAIL_CACHE_TTL_MS || '60000', 10);
const goodsDetailCache = new Map(); // key: goodsId, value: { data, expireAt }

function getCachedGoodsDetail(goodsId) {
  const cacheItem = goodsDetailCache.get(String(goodsId));
  if (!cacheItem) return null;
  if (cacheItem.expireAt <= Date.now()) {
    goodsDetailCache.delete(String(goodsId));
    return null;
  }
  return cacheItem.data;
}

function setCachedGoodsDetail(goodsId, data) {
  goodsDetailCache.set(String(goodsId), {
    data,
    expireAt: Date.now() + DEFAULT_CACHE_TTL_MS
  });
}

function invalidateGoodsDetailCacheById(goodsId) {
  goodsDetailCache.delete(String(goodsId));
}

async function invalidateGoodsDetailCacheByPropertyId(connection, propertyId) {
  // 通过规格ID找到商品ID并失效缓存
  const [rows] = await connection.execute(
    'SELECT goods_id FROM goods_properties WHERE id = ? LIMIT 1',
    [propertyId]
  );
  if (rows.length > 0 && rows[0].goods_id) {
    invalidateGoodsDetailCacheById(rows[0].goods_id);
  }
}

async function invalidateGoodsDetailCacheByValueId(connection, valueId) {
  // 通过规格值ID -> 规格ID -> 商品ID 失效缓存
  const [[valueRow]] = await connection.execute(
    'SELECT property_id FROM goods_property_values WHERE id = ? LIMIT 1',
    [valueId]
  );
  if (valueRow && valueRow.property_id) {
    await invalidateGoodsDetailCacheByPropertyId(connection, valueRow.property_id);
  }
}

// 标准化 is_default：仅当值数值等于 1 时视为默认
function normalizeDefaultFlag(value) {
  return Number(value) === 1 ? 1 : 0;
}

class Goods {
  /**
   * 批量构建商品数据的工具函数
   */
  static async buildGoodsData(connection, goods, withCategory = false) {
    if (goods.length === 0) return [];

    const goodsIds = goods.map(g => g.id);

    // 批量获取所有相关数据
    const promises = [];

    // 获取属性
    promises.push(connection.execute(`
      SELECT gp.id, gp.goods_id, gp.name, gp.\`desc\`, gp.is_open_checkbox
      FROM goods_properties gp
      WHERE gp.goods_id IN (${goodsIds.map(() => '?').join(',')})
      ORDER BY gp.goods_id ASC, gp.id ASC
    `, goodsIds));

    // 获取实体
    promises.push(connection.execute(`
      SELECT ge.entity_id as id, ge.goods_id, ge.spec_id, ge.trade_mark, ge.stock, 
             ge.image, ge.num, ge.price, ge.membership_price
      FROM goods_entities ge
      WHERE ge.goods_id IN (${goodsIds.map(() => '?').join(',')})
      ORDER BY ge.goods_id ASC, ge.id ASC
    `, goodsIds));

    // 获取图片
    promises.push(connection.execute(`
      SELECT gi.goods_id, gi.image_url
      FROM goods_images gi
      WHERE gi.goods_id IN (${goodsIds.map(() => '?').join(',')})
      ORDER BY gi.goods_id ASC, gi.sort ASC
    `, goodsIds));

    const [
      [allProperties],
      [allEntities], 
      [allImages]
    ] = await Promise.all(promises);

    // 获取属性值（如果有属性的话）
    let allPropertyValues = [];
    if (allProperties.length > 0) {
      const propertyIds = allProperties.map(p => p.id);
      [allPropertyValues] = await connection.execute(`
        SELECT gpv.id, gpv.property_id, gpv.value, gpv.code, gpv.is_default
        FROM goods_property_values gpv
        WHERE gpv.property_id IN (${propertyIds.map(() => '?').join(',')})
        ORDER BY gpv.property_id ASC, gpv.id ASC
      `, propertyIds);
    }

    // 建立索引
    const propertiesByGoods = {};
    const valuesByProperty = {};
    const entitiesByGoods = {};
    const imagesByGoods = {};

    allProperties.forEach(prop => {
      if (!propertiesByGoods[prop.goods_id]) {
        propertiesByGoods[prop.goods_id] = [];
      }
      propertiesByGoods[prop.goods_id].push(prop);
    });

    allPropertyValues.forEach(value => {
      if (!valuesByProperty[value.property_id]) {
        valuesByProperty[value.property_id] = [];
      }
      valuesByProperty[value.property_id].push(value);
    });

    allEntities.forEach(entity => {
      if (!entitiesByGoods[entity.goods_id]) {
        entitiesByGoods[entity.goods_id] = [];
      }
      entitiesByGoods[entity.goods_id].push(entity);
    });

    allImages.forEach(image => {
      if (!imagesByGoods[image.goods_id]) {
        imagesByGoods[image.goods_id] = [];
      }
      imagesByGoods[image.goods_id].push(image.image_url);
    });

    // 组装数据
    return goods.map(goodsItem => {
      // 组装商品属性
      const properties = propertiesByGoods[goodsItem.id] || [];
      const propertyList = properties.map(prop => {
        const values = valuesByProperty[prop.id] || [];
        return {
          id: prop.id,
          name: prop.name,
          desc: prop.desc,
          is_open_checkbox: Boolean(prop.is_open_checkbox),
          property_values: values.map(pv => ({
            id: pv.id,
            value: pv.value,
            code: pv.code,
            is_default: pv.is_default
          }))
        };
      });

      // 组装商品实体
      const entities = entitiesByGoods[goodsItem.id] || [];
      const entityList = entities.map(entity => ({
        id: entity.id,
        spec_id: entity.spec_id || "",
        trade_mark: entity.trade_mark || "",
        stock: entity.stock ? entity.stock.toString() : "0.00",
        spec_text: [],
        spec: [],
        image: entity.image || "",
        num: entity.num || 1,
        price: parseFloat(entity.price) || 0,
        membership_price: parseFloat(entity.membership_price) || 0
      }));

      // 获取商品图片
      const imageArr = imagesByGoods[goodsItem.id] || [];

      // 组装商品数据
      const result = {
        id: goodsItem.id,
        category_id: goodsItem.category_id,
        pack_cost: goodsItem.pack_cost ? goodsItem.pack_cost.toString() : "0.00",
        sales: goodsItem.sales || 0,
        goods_type: goodsItem.goods_type || 1,
        cover_img: goodsItem.cover_img || "",
        property: propertyList,
        entity: entityList,
        sort: goodsItem.sort || 0,
        price: parseFloat(goodsItem.price) || 0,
        unit: goodsItem.unit || "件",
        imageArr: imageArr,
        use_property: goodsItem.use_property || 0,
        content: goodsItem.content || "",
        stock: goodsItem.stock ? goodsItem.stock.toString() : "0.00",
        type: goodsItem.type || 1,
        is_label: goodsItem.is_label || 0,
        name: goodsItem.name || "",
        images: goodsItem.images || ""
      };

      if (withCategory && goodsItem.category_name) {
        result.category_name = goodsItem.category_name;
      }

      return result;
    });
  }

  /**
   * 获取所有商品（按分类组织，支持条件查询）
   * @param {number|null} categoryId - 可选的分类ID，不传则获取全部分类
   * @param {string|null} name - 可选的商品名称模糊查找
   */
  static async getAllGoodsWithFilter(categoryId = null, name = null) {
    const connection = await pool.getConnection();
    try {
      // 一次性查询所有需要的数据，只获取有商品的分类
      let query = `
        SELECT DISTINCT 
               gc.id as category_id, gc.sort as category_sort, gc.icon as category_icon, 
               gc.name as category_name, gc.is_show_backstage as category_is_show_backstage,
               g.id, g.name, g.price, g.stock, g.unit, g.content, 
               g.images, g.cover_img, g.pack_cost, g.sales, g.goods_type, 
               g.use_property, g.type, g.is_label, g.sort
        FROM goods_categories gc
        INNER JOIN goods g ON gc.id = g.category_id
      `;
      
      const params = [];
      const conditions = [];
      
      // 如果传入了分类ID，添加WHERE条件
      if (categoryId) {
        conditions.push('gc.id = ?');
        params.push(categoryId);
      }
      
      // 如果传入了商品名称，添加模糊查找条件
      if (name) {
        conditions.push('g.name LIKE ?');
        params.push(`%${name}%`);
      }
      
      // 如果有条件，添加WHERE子句
      if (conditions.length > 0) {
        query += ` WHERE ${conditions.join(' AND ')}`;
      }
      
      query += ` ORDER BY gc.sort ASC, g.sort ASC, g.id ASC`;

      const [rows] = await connection.execute(query, params);

      if (rows.length === 0) {
        return [];
      }

      // 提取唯一的分类信息
      const categoriesMap = new Map();
      const goodsByCategory = {};

      rows.forEach(row => {
        // 记录分类信息
        if (!categoriesMap.has(row.category_id)) {
          categoriesMap.set(row.category_id, {
            id: row.category_id,
            sort: row.category_sort || 0,
            icon: row.category_icon || "",
            name: row.category_name || "",
            is_show_backstage: row.category_is_show_backstage || 0
          });
          goodsByCategory[row.category_id] = [];
        }

        // 记录商品信息
        goodsByCategory[row.category_id].push({
          id: row.id,
          category_id: row.category_id,
          name: row.name,
          price: row.price,
          stock: row.stock,
          unit: row.unit,
          content: row.content,
          images: row.images,
          cover_img: row.cover_img,
          pack_cost: row.pack_cost,
          sales: row.sales,
          goods_type: row.goods_type,
          use_property: row.use_property,
          type: row.type,
          is_label: row.is_label,
          sort: row.sort
        });
      });

      // 批量构建所有商品的详细数据
      const allGoods = Object.values(goodsByCategory).flat();
      const detailedGoods = await this.buildGoodsData(connection, allGoods);

      // 重新按分类组织详细商品数据
      const detailedGoodsByCategory = {};
      detailedGoods.forEach(goods => {
        const categoryId = allGoods.find(g => g.id === goods.id)?.category_id;
        if (categoryId) {
          if (!detailedGoodsByCategory[categoryId]) {
            detailedGoodsByCategory[categoryId] = [];
          }
          detailedGoodsByCategory[categoryId].push(goods);
        }
      });

      // 构建最终结果，按分类排序
      const categories = Array.from(categoriesMap.values()).sort((a, b) => a.sort - b.sort);
      const result = categories.map(category => ({
        sort: category.sort,
        icon: category.icon,
        id: category.id,
        goods_list: detailedGoodsByCategory[category.id] || [],
        name: category.name,
        is_show_backstage: category.is_show_backstage
      }));

      // 计算商品总数
      const totalGoods = detailedGoods.length;

      return {
        total: totalGoods,
        data: result
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 获取所有商品（按分类组织）
   */
  static async getAllGoods() {
    const connection = await pool.getConnection();
    try {
      // 获取所有分类
      const [categories] = await connection.execute(`
        SELECT gc.id, gc.sort, gc.icon, gc.name, gc.is_show_backstage
        FROM goods_categories gc
        ORDER BY gc.sort ASC
      `);

      if (categories.length === 0) {
        return [];
      }

      const categoryIds = categories.map(c => c.id);

      // 批量获取所有商品
      const [allGoods] = await connection.execute(`
        SELECT g.id, g.category_id, g.name, g.price, g.stock, g.unit, g.content, 
               g.images, g.cover_img, g.pack_cost, g.sales, g.goods_type, 
               g.use_property, g.type, g.is_label, g.sort
        FROM goods g
        WHERE g.category_id IN (${categoryIds.map(() => '?').join(',')})
        ORDER BY g.category_id ASC, g.sort ASC, g.id ASC
      `, categoryIds);

      if (allGoods.length === 0) {
        return categories.map(cat => ({
          sort: cat.sort || 0,
          icon: cat.icon || "",
          id: cat.id,
          goods_list: [],
          name: cat.name || "",
          is_show_backstage: cat.is_show_backstage || 0
        }));
      }

      // 建立商品分类索引
      const goodsByCategory = {};
      allGoods.forEach(goods => {
        if (!goodsByCategory[goods.category_id]) {
          goodsByCategory[goods.category_id] = [];
        }
        goodsByCategory[goods.category_id].push(goods);
      });

      // 为每个分类批量构建商品数据
      const result = [];
      for (const category of categories) {
        const categoryGoods = goodsByCategory[category.id] || [];
        let goodsList = [];
        
        if (categoryGoods.length > 0) {
          goodsList = await this.buildGoodsData(connection, categoryGoods);
        }

        result.push({
          sort: category.sort || 0,
          icon: category.icon || "",
          id: category.id,
          goods_list: goodsList,
          name: category.name || "",
          is_show_backstage: category.is_show_backstage || 0
        });
      }

      return result;
    } finally {
      connection.release();
    }
  }

  /**
   * 获取销量排行榜商品（前7个）
   */
  static async getTopSales() {
    const connection = await pool.getConnection();
    try {
      // 获取销量最高的7个商品
      const [goods] = await connection.execute(`
        SELECT g.id, g.name, g.price, g.stock, g.unit, g.content, g.images, 
               g.cover_img, g.pack_cost, g.sales, g.goods_type, g.use_property, 
               g.type, g.is_label, g.sort, gc.name as category_name
        FROM goods g
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
        ORDER BY g.sales DESC
        LIMIT 7
      `);

      return await this.buildGoodsData(connection, goods, true);
    } finally {
      connection.release();
    }
  }

  /**
   * 获取商品（扁平结构，与getTopSales相同的数据格式）
   * @param {number|null} categoryId - 可选的分类ID，不传则获取全部商品
   * @param {string|null} name - 可选的商品名称模糊查找
   * @returns {Object} 返回包含商品列表和总数的对象
   */
  static async getAllGoodsFlat(categoryId = null, name = null) {
    const connection = await pool.getConnection();
    try {
      let query = `
        SELECT g.id, g.name, g.price, g.stock, g.unit, g.content, g.images, 
               g.cover_img, g.pack_cost, g.sales, g.goods_type, g.use_property, 
               g.type, g.is_label, g.sort, gc.name as category_name
        FROM goods g
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
      `;
      
      const params = [];
      const conditions = [];
      
      // 如果传入了分类ID，添加WHERE条件
      if (categoryId) {
        conditions.push('g.category_id = ?');
        params.push(categoryId);
      }
      
      // 如果传入了商品名称，添加模糊查找条件
      if (name) {
        conditions.push('g.name LIKE ?');
        params.push(`%${name}%`);
      }
      
      // 如果有条件，添加WHERE子句
      if (conditions.length > 0) {
        query += ` WHERE ${conditions.join(' AND ')}`;
      }
      
      query += ` ORDER BY g.sort ASC, g.id ASC`;

      const [goods] = await connection.execute(query, params);

      // 使用简化版本，只返回基本信息，提高性能
      const goodsList = goods.map(goodsItem => ({
        id: goodsItem.id,
        pack_cost: goodsItem.pack_cost ? goodsItem.pack_cost.toString() : "0.00",
        sales: goodsItem.sales || 0,
        goods_type: goodsItem.goods_type || 1,
        cover_img: goodsItem.cover_img || "",
        property: [], // 简化版本不包含详细属性
        entity: [], // 简化版本不包含详细实体
        sort: goodsItem.sort || 0,
        price: parseFloat(goodsItem.price) || 0,
        unit: goodsItem.unit || "件",
        imageArr: [], // 简化版本不包含详细图片数组
        use_property: goodsItem.use_property || 0,
        content: goodsItem.content || "",
        stock: goodsItem.stock ? goodsItem.stock.toString() : "0.00",
        type: goodsItem.type || 1,
        is_label: goodsItem.is_label || 0,
        name: goodsItem.name || "",
        images: goodsItem.images || "",
        category_name: goodsItem.category_name || ""
      }));

      return {
        total: goodsList.length,
        list: goodsList
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 获取商品（扁平结构，包含完整详情）
   * @param {number|null} categoryId - 可选的分类ID，不传则获取全部商品
   * @param {string|null} name - 可选的商品名称模糊查找
   * @returns {Object} 返回包含商品列表和总数的对象
   */
  static async getAllGoodsFlatDetailed(categoryId = null, name = null) {
    const connection = await pool.getConnection();
    try {
      let query = `
        SELECT g.id, g.name, g.price, g.stock, g.unit, g.content, g.images, 
               g.cover_img, g.pack_cost, g.sales, g.goods_type, g.use_property, 
               g.type, g.is_label, g.sort, gc.name as category_name
        FROM goods g
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
      `;
      
      const params = [];
      const conditions = [];
      
      // 如果传入了分类ID，添加WHERE条件
      if (categoryId) {
        conditions.push('g.category_id = ?');
        params.push(categoryId);
      }
      
      // 如果传入了商品名称，添加模糊查找条件
      if (name) {
        conditions.push('g.name LIKE ?');
        params.push(`%${name}%`);
      }
      
      // 如果有条件，添加WHERE子句
      if (conditions.length > 0) {
        query += ` WHERE ${conditions.join(' AND ')}`;
      }
      
      query += ` ORDER BY g.sort ASC, g.id ASC`;

      const [goods] = await connection.execute(query, params);

      const goodsList = await this.buildGoodsData(connection, goods, true);

      return {
        total: goodsList.length,
        list: goodsList
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 根据ID获取单个商品
   */
  static async getById(goodsId) {
    const connection = await pool.getConnection();
    try {
      // 命中缓存直接返回（确保包含 category_id）
      const cached = getCachedGoodsDetail(goodsId);
      if (cached && cached.category_id !== undefined) {
        return cached;
      }

      // 获取商品基本信息
      const [goods] = await connection.execute(`
        SELECT g.*, gc.name as category_name
        FROM goods g
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
        WHERE g.id = ?
      `, [goodsId]);

      if (goods.length === 0) {
        return null;
      }

      const result = await this.buildGoodsData(connection, goods, true);
      const detail = result[0];
      // 写入缓存
      setCachedGoodsDetail(goodsId, detail);
      return detail;
    } finally {
      connection.release();
    }
  }

  /**
   * 创建新商品
   */
  static async create(goodsData) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      const {
        category_id,
        name,
        price = 0,
        stock = 0,
        unit = '件',
        content = '',
        image = '',
        pack_cost = 0,
        sales = 0,
        goods_type = 1,
        use_property = 0,
        type = 1,
        is_label = 0,
        sort = 0,
        property_ids = [],
        entity = {}
      } = goodsData;

      // 验证必需字段
      if (!category_id || !name) {
        throw new Error('分类ID和商品名称为必填项');
      }

      // 检查分类是否存在
      const [categories] = await connection.execute(
        'SELECT id FROM goods_categories WHERE id = ?',
        [category_id]
      );

      if (categories.length === 0) {
        throw new Error('指定的商品分类不存在');
      }

      // 创建商品
      const [goodsResult] = await connection.execute(`
        INSERT INTO goods (
          category_id, name, price, stock, unit, content, images,
          pack_cost, sales, goods_type, use_property, type, is_label, sort
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        category_id, name, price, stock, unit, content, image,
        pack_cost, sales, goods_type, use_property, type, is_label, sort
      ]);

      const goodsId = goodsResult.insertId;

      // 挂载现有规格（如果有）
      if (property_ids && property_ids.length > 0) {
        // 验证规格ID是否存在
        const [existingProperties] = await connection.execute(`
          SELECT id FROM goods_properties 
          WHERE id IN (${property_ids.map(() => '?').join(',')})
        `, property_ids);

        if (existingProperties.length !== property_ids.length) {
          throw new Error('部分规格ID不存在');
        }

        // 将规格挂载到商品上（更新goods_id）
        await connection.execute(`
          UPDATE goods_properties 
          SET goods_id = ? 
          WHERE id IN (${property_ids.map(() => '?').join(',')})
        `, [goodsId, ...property_ids]);

        // 更新商品使用规格标志
        await connection.execute(
          'UPDATE goods SET use_property = 1 WHERE id = ?',
          [goodsId]
        );
      }

      // 创建商品实体
      const entityId = entity.entity_id || `${Date.now().toString(36)}${Math.random().toString(36).substr(2, 9)}`.substr(0, 16);
      await connection.execute(`
        INSERT INTO goods_entities (
          entity_id, goods_id, spec_id, trade_mark, stock, image, num, price, membership_price
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        entityId,
        goodsId,
        entity.spec_id || '',
        entity.trade_mark || '',
        entity.stock || stock,
        entity.image || '',
        entity.num || 1,
        entity.price || price,
        entity.membership_price || 0
      ]);

      // 如果有单张图片，存储到goods_images表中
      if (image) {
        await connection.execute(`
          INSERT INTO goods_images (goods_id, image_url, sort)
          VALUES (?, ?, ?)
        `, [goodsId, image, 0]);
      }

      await connection.commit();

      // 失效新建商品缓存（防止旧数据或空）
      invalidateGoodsDetailCacheById(goodsId);

      return {
        goods_id: goodsId,
        entity_id: entityId
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 删除商品
   */
  static async delete(goodsId) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      // 检查商品是否存在
      const [goods] = await connection.execute(
        'SELECT id FROM goods WHERE id = ?',
        [goodsId]
      );

      if (goods.length === 0) {
        throw new Error('商品不存在');
      }

      // 删除商品图片
      await connection.execute('DELETE FROM goods_images WHERE goods_id = ?', [goodsId]);

      // 删除商品实体
      await connection.execute('DELETE FROM goods_entities WHERE goods_id = ?', [goodsId]);

      // 删除商品主记录
      const [result] = await connection.execute('DELETE FROM goods WHERE id = ?', [goodsId]);

      await connection.commit();

      // 失效缓存
      invalidateGoodsDetailCacheById(goodsId);

      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 更新商品
   */
  static async update(goodsId, goodsData) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      // 检查商品是否存在
      const [goods] = await connection.execute(
        'SELECT id FROM goods WHERE id = ?',
        [goodsId]
      );

      if (goods.length === 0) {
        throw new Error('商品不存在');
      }

      // 构建更新字段
      const updateFields = [];
      const updateValues = [];

      const allowedFields = [
        'category_id', 'name', 'price', 'stock', 'unit', 'content', 'images',
        'cover_img', 'pack_cost', 'sales', 'goods_type', 'use_property',
        'type', 'is_label', 'sort'
      ];

      allowedFields.forEach(field => {
        if (goodsData[field] !== undefined) {
          updateFields.push(`${field} = ?`);
          updateValues.push(goodsData[field]);
        }
      });

      // 兼容创建参数：如果传入 image，则同时更新 images 字段
      if (goodsData.image !== undefined) {
        updateFields.push('images = ?');
        updateValues.push(goodsData.image || '');
      }

      // 如提供字段，则更新商品主表
      let result = { affectedRows: 0 };
      if (updateFields.length > 0) {
        updateValues.push(goodsId);
        [result] = await connection.execute(
          `UPDATE goods SET ${updateFields.join(', ')} WHERE id = ?`,
          updateValues
        );
      }

      // 如果传入 image，则同步到 goods_images (sort=0)
      if (goodsData.image !== undefined) {
        await connection.execute('DELETE FROM goods_images WHERE goods_id = ? AND sort = 0', [goodsId]);
        if (goodsData.image) {
          await connection.execute(
            'INSERT INTO goods_images (goods_id, image_url, sort) VALUES (?, ?, ?)',
            [goodsId, goodsData.image, 0]
          );
        }
      }

      // 如果传入 property_ids，则清空旧关联并按传入值重建关联（完全覆盖）
      if (Array.isArray(goodsData.property_ids)) {
        const uniquePropertyIds = [...new Set(goodsData.property_ids)];

        // 1) 先清空当前商品的所有规格关联
        await connection.execute('UPDATE goods_properties SET goods_id = NULL WHERE goods_id = ?', [goodsId]);

        // 2) 如果有新的规格列表，则校验并关联到当前商品
        if (uniquePropertyIds.length > 0) {
          const placeholders = uniquePropertyIds.map(() => '?').join(',');
          const [existingProperties] = await connection.execute(
            `SELECT id FROM goods_properties WHERE id IN (${placeholders})`,
            uniquePropertyIds
          );
          if (existingProperties.length !== uniquePropertyIds.length) {
            throw new Error('部分规格ID不存在');
          }
          await connection.execute(
            `UPDATE goods_properties SET goods_id = ? WHERE id IN (${placeholders})`,
            [goodsId, ...uniquePropertyIds]
          );
        }

        // 3) 同步 use_property 标志
        await connection.execute('UPDATE goods SET use_property = ? WHERE id = ?', [uniquePropertyIds.length > 0 ? 1 : 0, goodsId]);
      }

      // 如果传入 entity，则进行 upsert（存在则更新，不存在则创建）
      if (goodsData.entity && typeof goodsData.entity === 'object') {
        const e = goodsData.entity;
        if (e.entity_id) {
          const [exist] = await connection.execute(
            'SELECT entity_id FROM goods_entities WHERE entity_id = ? AND goods_id = ? LIMIT 1',
            [e.entity_id, goodsId]
          );
          if (exist.length > 0) {
            await connection.execute(
              `UPDATE goods_entities
               SET spec_id = ?, trade_mark = ?, stock = ?, image = ?, num = ?, price = ?, membership_price = ?
               WHERE entity_id = ? AND goods_id = ?`,
              [e.spec_id || '', e.trade_mark || '', e.stock || goodsData.stock || 0, e.image || '', e.num || 1, e.price || goodsData.price || 0, e.membership_price || 0, e.entity_id, goodsId]
            );
          } else {
            await connection.execute(
              `INSERT INTO goods_entities (entity_id, goods_id, spec_id, trade_mark, stock, image, num, price, membership_price)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
              [e.entity_id, goodsId, e.spec_id || '', e.trade_mark || '', e.stock || goodsData.stock || 0, e.image || '', e.num || 1, e.price || goodsData.price || 0, e.membership_price || 0]
            );
          }
        } else {
          const newEntityId = `${Date.now().toString(36)}${Math.random().toString(36).substr(2, 9)}`.substr(0, 16);
          await connection.execute(
            `INSERT INTO goods_entities (entity_id, goods_id, spec_id, trade_mark, stock, image, num, price, membership_price)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [newEntityId, goodsId, e.spec_id || '', e.trade_mark || '', e.stock || goodsData.stock || 0, e.image || '', e.num || 1, e.price || goodsData.price || 0, e.membership_price || 0]
          );
        }
      }

      await connection.commit();

      // 失效缓存
      invalidateGoodsDetailCacheById(goodsId);

      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  /**
   * 创建商品规格
   */
  static async createProperty(goodsId, propertyData) {
    const connection = await pool.getConnection();
    try {
      const { name, desc, is_open_checkbox } = propertyData;

      if (!name) {
        throw new Error('规格名称不能为空');
      }

      // 检查商品是否存在
      const [goods] = await connection.execute(
        'SELECT id FROM goods WHERE id = ?',
        [goodsId]
      );

      if (goods.length === 0) {
        throw new Error('商品不存在');
      }

      // 创建规格
      const [result] = await connection.execute(`
        INSERT INTO goods_properties (goods_id, name, \`desc\`, is_open_checkbox)
        VALUES (?, ?, ?, ?)
      `, [goodsId, name, desc || null, is_open_checkbox ? 1 : 0]);

      // 失效缓存
      invalidateGoodsDetailCacheById(goodsId);

      return result.insertId;
    } finally {
      connection.release();
    }
  }

  /**
   * 创建未绑定商品的规格（不需要传id或goodsId）
   */
  static async createStandaloneProperty(propertyData) {
    const connection = await pool.getConnection();
    try {
      const { name, desc, is_open_checkbox } = propertyData;

      if (!name) {
        throw new Error('规格名称不能为空');
      }

      const [result] = await connection.execute(`
        INSERT INTO goods_properties (goods_id, name, \`desc\`, is_open_checkbox)
        VALUES (NULL, ?, ?, ?)
      `, [name, desc || null, is_open_checkbox ? 1 : 0]);

      return result.insertId;
    } finally {
      connection.release();
    }
  }

  /**
   * 统一创建规格：可选绑定多个商品
   * @param {Object} payload { name, desc, is_open_checkbox, goodsIds?: number[] }
   * - 若提供 goodsIds（可为空数组），创建规格后批量绑定到这些商品
   */
  static async createPropertyUnified(payload) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      const { name, desc, is_open_checkbox } = payload || {};
      // 支持 goodsIds 和 goods_ids 两种入参
      const goodsIdsInput = (payload && (payload.goodsIds || payload.goods_ids)) || null;
      const propertyValuesInput = (payload && payload.property_values) || null;
      if (!name) {
        throw new Error('规格名称不能为空');
      }

      // 规范化商品ID数组
      const uniqueGoodsIds = Array.isArray(goodsIdsInput)
        ? [...new Set(goodsIdsInput)].map(id => Number(id)).filter(id => Number.isFinite(id))
        : [];

      // 若提供了商品ID，先校验存在性
      if (uniqueGoodsIds.length > 0) {
        const placeholders = uniqueGoodsIds.map(() => '?').join(',');
        const [exists] = await connection.execute(
          `SELECT id FROM goods WHERE id IN (${placeholders})`,
          uniqueGoodsIds
        );
        if (exists.length !== uniqueGoodsIds.length) {
          throw new Error('部分商品ID不存在');
        }
      }

      // 创建首个规格：若有目标商品则直接绑定到第一个商品，否则为独立规格
      const firstGoodsId = uniqueGoodsIds.length > 0 ? uniqueGoodsIds[0] : null;
      const [propRes] = await connection.execute(`
        INSERT INTO goods_properties (goods_id, name, \`desc\`, is_open_checkbox)
        VALUES (?, ?, ?, ?)
      `, [firstGoodsId, name, desc || null, is_open_checkbox ? 1 : 0]);

      const primaryPropertyId = propRes.insertId;

      // 如果还有其他商品，批量复制规格并绑定
      let duplicatedPropertyIds = [];
      if (uniqueGoodsIds.length > 1) {
        const restGoodsIds = uniqueGoodsIds.slice(1);
        const insertValues = [];
        const insertParams = [];
        for (const gid of restGoodsIds) {
          insertValues.push('(?, ?, ?, ?)');
          insertParams.push(gid, name, desc || null, is_open_checkbox ? 1 : 0);
        }
        const [multiRes] = await connection.execute(
          `INSERT INTO goods_properties (goods_id, name, \`desc\`, is_open_checkbox) VALUES ${insertValues.join(',')}`,
          insertParams
        );
        const firstInsertId = multiRes.insertId;
        for (let i = 0; i < restGoodsIds.length; i++) {
          duplicatedPropertyIds.push(firstInsertId + i);
        }
      }

      // 规格值：如果传入 property_values，则为所有创建的规格（首个+复制）批量创建同样的规格值
      if (Array.isArray(propertyValuesInput) && propertyValuesInput.length > 0) {
        const allPropertyIds = [primaryPropertyId, ...duplicatedPropertyIds];
        const valueTuples = [];
        const valueParams = [];
        for (const pid of allPropertyIds) {
          for (const v of propertyValuesInput) {
            if (!v || v.value === undefined || v.value === null || v.value === '') continue;
            valueTuples.push('(?, ?, ?, ?)');
            valueParams.push(pid, v.value, null, normalizeDefaultFlag(v.is_default));
          }
        }
        if (valueTuples.length > 0) {
          await connection.execute(
            `INSERT INTO goods_property_values (property_id, value, code, is_default) VALUES ${valueTuples.join(',')}`,
            valueParams
          );
        }
      }

      // 如果绑定了商品，统一设置 use_property = 1 并失效缓存
      if (uniqueGoodsIds.length > 0) {
        const placeholders = uniqueGoodsIds.map(() => '?').join(',');
        await connection.execute(
          `UPDATE goods SET use_property = 1 WHERE id IN (${placeholders})`,
          uniqueGoodsIds
        );
        for (const gid of uniqueGoodsIds) {
          invalidateGoodsDetailCacheById(gid);
        }
      }

      await connection.commit();
      return primaryPropertyId;
    } catch (e) {
      await connection.rollback();
      throw e;
    } finally {
      connection.release();
    }
  }

  /**
   * 更新商品规格
   */
  static async updateProperty(propertyId, propertyData) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const { name, desc, is_open_checkbox } = propertyData;
      const goodsIdsInput = propertyData && (propertyData.goodsIds || propertyData.goods_ids);
      const propertyValuesInput = propertyData && propertyData.property_values;

      // 检查规格是否存在
      const [properties] = await connection.execute(
        'SELECT id, goods_id FROM goods_properties WHERE id = ?',
        [propertyId]
      );

      if (properties.length === 0) {
        throw new Error('规格不存在');
      }

      // 构建更新字段
      const updateFields = [];
      const updateValues = [];

      if (name !== undefined) {
        updateFields.push('name = ?');
        updateValues.push(name);
      }

      if (desc !== undefined) {
        updateFields.push('`desc` = ?');
        updateValues.push(desc);
      }

      if (is_open_checkbox !== undefined) {
        updateFields.push('is_open_checkbox = ?');
        updateValues.push(is_open_checkbox ? 1 : 0);
      }

      if (updateFields.length === 0) {
        throw new Error('没有提供要更新的字段');
      }

      updateValues.push(propertyId);

      const [result] = await connection.execute(
        `UPDATE goods_properties SET ${updateFields.join(', ')} WHERE id = ?`,
        updateValues
      );

      // 如果传入 property_values，则替换当前规格的全部规格值
      if (Array.isArray(propertyValuesInput)) {
        await connection.execute('DELETE FROM goods_property_values WHERE property_id = ?', [propertyId]);
        if (propertyValuesInput.length > 0) {
          const tuples = [];
          const params = [];
          for (const v of propertyValuesInput) {
            if (!v || v.value === undefined || v.value === null || v.value === '') continue;
            tuples.push('(?, ?, ?, ?)');
            params.push(propertyId, v.value, null, normalizeDefaultFlag(v.is_default));
          }
          if (tuples.length > 0) {
            await connection.execute(
              `INSERT INTO goods_property_values (property_id, value, code, is_default) VALUES ${tuples.join(',')}`,
              params
            );
          }
        }
      }

      // 如果传入 goodsIds/goods_ids，则将该规格按提交的商品集合进行绑定：
      // - 当前规格绑定到第一个商品（或置空）
      // - 其余商品复制新规格，并复制规格值
      if (Array.isArray(goodsIdsInput)) {
        const uniqueGoodsIds = [...new Set(goodsIdsInput)].map(id => Number(id)).filter(id => Number.isFinite(id));

        if (uniqueGoodsIds.length > 0) {
          const placeholders = uniqueGoodsIds.map(() => '?').join(',');
          const [exists] = await connection.execute(
            `SELECT id FROM goods WHERE id IN (${placeholders})`,
            uniqueGoodsIds
          );
          if (exists.length !== uniqueGoodsIds.length) {
            throw new Error('部分商品ID不存在');
          }

          const firstGoodsId = uniqueGoodsIds[0];
          await connection.execute('UPDATE goods_properties SET goods_id = ? WHERE id = ?', [firstGoodsId, propertyId]);

          // 复制当前规格的规格值，作为模板
          const [currentValues] = await connection.execute(
            'SELECT value, code, is_default FROM goods_property_values WHERE property_id = ? ORDER BY id ASC',
            [propertyId]
          );

          if (uniqueGoodsIds.length > 1) {
            const restGoodsIds = uniqueGoodsIds.slice(1);
            // 批量插入复制的规格
            const propTuples = [];
            const propParams = [];
            for (const gid of restGoodsIds) {
              propTuples.push('(?, ?, ?, ?)');
              propParams.push(gid, name !== undefined ? name : (propertyData.property_name || ''), desc !== undefined ? desc : (propertyData.property_desc || null), is_open_checkbox !== undefined ? (is_open_checkbox ? 1 : 0) : 0);
            }
            if (propTuples.length > 0) {
              const [multiRes] = await connection.execute(
                `INSERT INTO goods_properties (goods_id, name, \`desc\`, is_open_checkbox) VALUES ${propTuples.join(',')}`,
                propParams
              );
              const baseId = multiRes.insertId;
              const newPropIds = restGoodsIds.map((_, idx) => baseId + idx);

              // 批量为新规格插入规格值
              if (currentValues.length > 0) {
                const valueTuples = [];
                const valueParams = [];
                for (const npid of newPropIds) {
                  for (const v of currentValues) {
                    valueTuples.push('(?, ?, ?, ?)');
                    valueParams.push(npid, v.value, v.code || null, normalizeDefaultFlag(v.is_default));
                  }
                }
                if (valueTuples.length > 0) {
                  await connection.execute(
                    `INSERT INTO goods_property_values (property_id, value, code, is_default) VALUES ${valueTuples.join(',')}`,
                    valueParams
                  );
                }
              }
            }
          }

          // 设置这些商品的 use_property = 1
          await connection.execute(
            `UPDATE goods SET use_property = 1 WHERE id IN (${placeholders})`,
            uniqueGoodsIds
          );

          // 失效缓存
          for (const gid of uniqueGoodsIds) {
            invalidateGoodsDetailCacheById(gid);
          }
        } else {
          // 空数组表示不绑定任何商品
          await connection.execute('UPDATE goods_properties SET goods_id = NULL WHERE id = ?', [propertyId]);
        }
      }

      await connection.commit();

      // 失效缓存（通过规格定位）
      await invalidateGoodsDetailCacheByPropertyId(connection, propertyId);

      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  /**
   * 删除商品规格
   */
  static async deleteProperty(propertyId) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      // 检查规格是否存在
      const [properties] = await connection.execute(
        'SELECT id, goods_id FROM goods_properties WHERE id = ?',
        [propertyId]
      );

      if (properties.length === 0) {
        throw new Error('规格不存在');
      }

      // 删除规格值
      await connection.execute('DELETE FROM goods_property_values WHERE property_id = ?', [propertyId]);

      // 删除规格
      const [result] = await connection.execute('DELETE FROM goods_properties WHERE id = ?', [propertyId]);

      await connection.commit();

      // 失效缓存
      await invalidateGoodsDetailCacheByPropertyId(connection, propertyId);

      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 创建规格值
   */
  static async createPropertyValue(propertyId, valueData) {
    const connection = await pool.getConnection();
    try {
      const { value, code, is_default } = valueData;

      if (!value) {
        throw new Error('规格值不能为空');
      }

      // 检查规格是否存在
      const [properties] = await connection.execute(
        'SELECT id, goods_id FROM goods_properties WHERE id = ?',
        [propertyId]
      );

      if (properties.length === 0) {
        throw new Error('规格不存在');
      }

      // 创建规格值
      const [result] = await connection.execute(`
        INSERT INTO goods_property_values (property_id, value, code, is_default)
        VALUES (?, ?, ?, ?)
      `, [
        propertyId,
        value,
        code || `CODE_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
        normalizeDefaultFlag(is_default)
      ]);

      // 失效缓存
      await invalidateGoodsDetailCacheByPropertyId(connection, propertyId);

      return result.insertId;
    } finally {
      connection.release();
    }
  }

  /**
   * 更新规格值
   */
  static async updatePropertyValue(valueId, valueData) {
    const connection = await pool.getConnection();
    try {
      const { value, code, is_default } = valueData;

      // 检查规格值是否存在
      const [values] = await connection.execute(
        'SELECT id, property_id FROM goods_property_values WHERE id = ?',
        [valueId]
      );

      if (values.length === 0) {
        throw new Error('规格值不存在');
      }

      // 构建更新字段
      const updateFields = [];
      const updateValues = [];

      if (value !== undefined) {
        updateFields.push('value = ?');
        updateValues.push(value);
      }

      if (code !== undefined) {
        updateFields.push('code = ?');
        updateValues.push(code);
      }

      if (is_default !== undefined) {
        updateFields.push('is_default = ?');
        updateValues.push(normalizeDefaultFlag(is_default));
      }

      if (updateFields.length === 0) {
        throw new Error('没有提供要更新的字段');
      }

      updateValues.push(valueId);

      const [result] = await connection.execute(
        `UPDATE goods_property_values SET ${updateFields.join(', ')} WHERE id = ?`,
        updateValues
      );

      // 失效缓存
      await invalidateGoodsDetailCacheByValueId(connection, valueId);

      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  /**
   * 删除规格值
   */
  static async deletePropertyValue(valueId) {
    const connection = await pool.getConnection();
    try {
      // 检查规格值是否存在
      const [values] = await connection.execute(
        'SELECT id, property_id FROM goods_property_values WHERE id = ?',
        [valueId]
      );

      if (values.length === 0) {
        throw new Error('规格值不存在');
      }

      const [result] = await connection.execute('DELETE FROM goods_property_values WHERE id = ?', [valueId]);

      // 失效缓存
      await invalidateGoodsDetailCacheByValueId(connection, valueId);

      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  /**
   * 获取规格列表（包含规格值）
   * @param {number|null} goodsId - 可选的商品ID，不传则获取全部规格
   * @param {number|null} categoryId - 可选的分类ID，不传则获取全部分类的规格
   * @returns {Array} 规格列表，每个规格包含完整的规格值信息
   */
  static async getAllProperties(goodsId = null, categoryId = null) {
    const connection = await pool.getConnection();
    try {
      // 使用LEFT JOIN一次性查询所有规格和规格值，提高性能
      let query = `
        SELECT 
          gp.id as property_id,
          gp.goods_id,
          gp.name as property_name,
          gp.\`desc\` as property_desc,
          gp.is_open_checkbox,
          g.name as goods_name,
          gc.id as category_id,
          gc.name as category_name,
          gpv.id as value_id,
          gpv.value as value_name,
          gpv.code as value_code,
          gpv.is_default
        FROM goods_properties gp
        LEFT JOIN goods g ON gp.goods_id = g.id
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
        LEFT JOIN goods_property_values gpv ON gp.id = gpv.property_id
      `;
      
      const params = [];
      const conditions = [];
      
      // 如果传入了商品ID，添加WHERE条件
      if (goodsId) {
        conditions.push('gp.goods_id = ?');
        params.push(goodsId);
      }
      
      // 如果传入了分类ID，添加WHERE条件
      if (categoryId) {
        conditions.push('gc.id = ?');
        params.push(categoryId);
      }
      
      // 如果有条件，添加WHERE子句
      if (conditions.length > 0) {
        query += ` WHERE ${conditions.join(' AND ')}`;
      }
      
      query += ` ORDER BY gp.goods_id ASC, gp.id ASC, gpv.id ASC`;

      const [rows] = await connection.execute(query, params);

      // 组织数据结构：以规格为主体，包含规格值数组
      const propertiesMap = new Map();
      
      rows.forEach(row => {
        if (!row.property_id) return; // 跳过没有规格的记录
        
        // 如果规格还没有被添加到Map中
        if (!propertiesMap.has(row.property_id)) {
          propertiesMap.set(row.property_id, {
            id: row.property_id,
            name: row.property_name,
            desc: row.property_desc,
            is_open_checkbox: Boolean(row.is_open_checkbox),
            goods_id: row.goods_id,
            goods_name: row.goods_name,
            category_id: row.category_id,
            category_name: row.category_name,
            property_values: []
          });
        }
        
        // 添加规格值（如果存在的话）
        if (row.value_id) {
          const property = propertiesMap.get(row.property_id);
          property.property_values.push({
            id: row.value_id,
            value: row.value_name,
            code: row.value_code,
            is_default: Boolean(row.is_default)
          });
        }
      });
      
      return Array.from(propertiesMap.values());
    } finally {
      connection.release();
    }
  }

  /**
   * 获取简化的规格统计信息
   * @returns {Object} 包含规格统计信息的对象
   */
  static async getPropertiesStats() {
    const connection = await pool.getConnection();
    try {
      const [stats] = await connection.execute(`
        SELECT 
          COUNT(DISTINCT gp.id) as total_properties,
          COUNT(DISTINCT gp.goods_id) as goods_with_properties,
          COUNT(DISTINCT gpv.id) as total_property_values,
          COUNT(DISTINCT gc.id) as categories_with_properties
        FROM goods_properties gp
        LEFT JOIN goods g ON gp.goods_id = g.id
        LEFT JOIN goods_categories gc ON g.category_id = gc.id
        LEFT JOIN goods_property_values gpv ON gp.id = gpv.property_id
      `);
      
      return stats[0] || {
        total_properties: 0,
        goods_with_properties: 0,
        total_property_values: 0,
        categories_with_properties: 0
      };
    } finally {
      connection.release();
    }
  }
}

module.exports = Goods;
